import sys
sys.path.append("../")

from services.BaseService import *
from model.CommonDb import *

class DataService(BaseService):
    def __init__(self):
        super(DataService,self).__init__()

    def getData(self,url,params):
        options = url.split("/")
        if options[1] == "query":
            return self.queryListByReg(options[2],options[3],options[4])
        elif options[1] == "gets":
            return self.gets(options[2])
        elif options[1] == "get":
            return self.get(options[2],options[3])
        elif options[1] == "add":
            return self.add(options[2],params)
        elif options[1] == "update":
            return self.update(options[2],options[3],params)
        elif options[1] == "delete":
            return self.delete(options[2],options[3])
        elif options[1] == "getslimit":
            return self.getslimit(options[2],options[3],options[4])
        elif options[1] == "query":
            return self.queryListByReg(options[2],options[3],options[4])
        elif options[1] == "querylimit":
            return self.queryListByRegAndLimit(options[2],options[3],options[4],options[5],options[6])
        elif options[1] == "get_structure":
            return self.get_structure(options[2])
        elif options[1] == "get_tree":
            return self.get_tree(options[2],options[3],options[4])
        list = []
        return list

    def queryOne(self,tablename, select_expression, where_expression):
        db = CommonDb(tablename, "gaokao")
        sql = "select " + select_expression + " from " + tablename + " where 1 and " + where_expression + ";";
        rs = db.executeMap(sql)
        if len(rs) > 0:
            return rs[0]
        else:
            return []
    def getsLimit(self, tablename, begin, count):
        rs = self.queryOne(tablename, "count(1) as num", "1")
        res = {}
        if "id" in rs:
            res["num"] = rs["id"]
        res["data"] = self.queryList(tablename, "*", "1 limit " + begin + "," + count);
        return res

    def queryListByRegAndLimit(self,tablename, select_expression, where_expression, begin, count):
        select_str = self.parseSelectExpression(select_expression);
        where_str = self.parseWhereExpression(where_expression);
        rs = self.queryOne(tablename, "count(1) as num", "1 and " + where_str)
        res = {}
        if "id" in rs:
            res["num"] = rs["id"]
        res["data"] = self.queryList(tablename, select_str,"1  and " + where_str + " limit " + begin + "," + count)
        return res

    def queryListByReg(self,tablename,select_expression,where_expression):
        db = CommonDb(tablename, "gaokao")
        select_str = self.parseSelectExpression(select_expression)
        where_str = self.parseWhereExpression(where_expression)
        sql = "select " + select_str + " from " + tablename + " where 1 and " + where_str + ";";
        return db.executeMap(sql)

    def gets(self,tablename):
        return self.queryList(tablename,"*","1")

    def queryList(self,tablename, select_expression, where_expression):
        db = CommonDb(tablename, "gaokao")
        sql = "select " + select_expression + " from " + tablename + " where 1 and " + where_expression + ";";
        return db.executeMap(sql)

    def get(self,tablename, id):
        db = CommonDb(tablename, "gaokao")
        sql = "select * from " + tablename + " where id="+str(id)+";"
        return db.executeMap(sql)

    def add(self,tablename, params):
        db = CommonDb(tablename, "gaokao")
        return db.add(params)

    def update(self,tablename, id, params):
        db = CommonDb(tablename, "gaokao")
        return db.update(params," id=" + str(id))

    def delete(self,tablename, id):
        db = CommonDb(tablename, "gaokao")
        return db.delete_where(" id=" + str(id))

    def query(self,tablename, selectreg, wherereg):
        return self.queryListByReg(tablename,selectreg,wherereg)

    def querylimit(self, tablename, selectreg, wherereg, begin, count):
        return self.queryListByRegAndLimit(tablename,selectreg,wherereg,begin,count)

    def get_structure(self,tablename):
        db = CommonDb(tablename, "gaokao")
        sql = "show full columns from " + tablename + ";"
        return db.executeDescMap(sql)

    def get_tree(self,tablename, id, level):
        db = CommonDb(tablename, "gaokao")
        sql = ""
        if level == "9999":
            sql = sql + "   select * from (\n              select t1.*, \n              if(find_in_set(parent_id, @pids) > 0, @pids := concat(@pids, ',', id), 0) as ischild\n              from (\n                   select * from " + tablename + " t order by t.id asc\n                  ) t1,\n                  (select @pids := '" + id + "' ) t2\n             ) t3 where ischild != 0"
        else:
            sql = sql + "   select * from (\n              select t1.*, \n              if(find_in_set(parent_id, @pids) > 0, @pids := concat(@pids, ',', id), 0) as ischild\n              from (\n                   select * from " + tablename + " t order by t.id asc\n                  ) t1,\n                  (select @pids := '" + id + "' ) t2\n             ) t3 where ischild != 0 and level=" + level

        return db.executeMap(sql)
